package com.dp.appi.dao.impl;

import com.dp.appi.dao.ProjectLogDao;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created by bchen on 2016/5/25.
 */

@Component
public class ProjectLogDaoImpl extends BaseDaoImpl implements ProjectLogDao {

    /**
     * 项目日志搜索
     *
     * @param param
     * @param users
     * @return
     */
    @Override
    public List<Map<String, Object>> logList(Map<String, String> param, String users) {

        String startDate = param.get("startDate");
        String endDate = param.get("endDate");
        String s = param.get("s");
        String n = param.get("n");
        List p = new ArrayList();

        StringBuffer sql = new StringBuffer();
        sql.append("select  a.log_id,b.realname user,a.voice_remark,a.characters_remark,");
        sql.append("date_format(a.create_time, '%Y-%m-%d %H:%i:%s') create_time ");
        sql.append("from p_log a ");
        sql.append("LEFT JOIN t_s_base_user b ");
        sql.append("on a.user_id = b.ID ");
        sql.append("where a.user_id in ('"+users+"') ");

        if(null != startDate&&!"".equals(startDate)){
            sql.append("and date_format(?, '%Y-%m-%d') <= date_format(a.create_time,'%Y-%m-%d') ");
            p.add(startDate);
        }
        if(null != endDate&&!"".equals(endDate)){
            sql.append("and date_format(?, '%Y-%m-%d') >= date_format(a.create_time,'%Y-%m-%d') ");
            p.add(endDate);
        }
        p.add(Integer.parseInt(s));
        p.add(Integer.parseInt(n));
        sql.append(" order by a.create_time desc ");
        sql.append(" limit ?,? ");
        Object[] paramObj = new Object[p.size()];
        for(int i=0;i<p.size();i++){
            paramObj[i] = p.get(i);
        }
        return getJdbcTemplate().queryForList(sql.toString(),paramObj);
    }

    /**
     * 项目日志详细
     *
     * @param log_id
     * @return
     */
    @Override
    public List<Map<String, Object>> logDetailList(Long log_id) {
        StringBuffer sql = new StringBuffer();
        sql.append("select b.`name` stage_name,a.statistics project_statis  ");
        sql.append("from p_log_detail a ");
        sql.append("LEFT JOIN p_stage b ");
        sql.append("on a.stage_id=b.stage_id ");
        sql.append(" where log_id = ?");
        return getJdbcTemplate().queryForList(sql.toString(),log_id);
    }

    /**
     * 填报日志
     *
     * @param paramMap
     * @return
     */
    @Override
    public Long assessedLog(Map<String,Object>  paramMap ) {
        StringBuffer sql = new StringBuffer();
        sql.append("INSERT INTO `p_log` ");
        sql.append("( `user_id`, `voice_remark`, `characters_remark`) ");
        sql.append("VALUES ");
        sql.append("(:user_id, :voice_remark, :characters_remark)");
        Long id = executeSqlReturnKey(sql.toString(),paramMap);
        return id;
    }

    /**
     * 填报日志详细
     *
     * @param paramMap
     */
    @Override
    public void assessedLogDetail(Map<String,Object>  paramMap ) {
        StringBuffer sql = new StringBuffer();
        sql.append("INSERT INTO `p_log_detail` ");
        sql.append("(`log_id`, `stage_id`, `statistics`) ");
        sql.append("  VALUES ");
        sql.append("(:log_id,:stage_id,:statistics)");
        executeSqlReturnKey(sql.toString(),paramMap);
    }

    /**
     * 获取当日电压等级里程
     *
     * @param id
     * @return
     */
    @Override
    public List<Map<String, Object>> getVolMlByToday(String id) {
        StringBuffer sql = new StringBuffer();
        sql.append("select t.stage_id,t.stage_name,CONCAT('踏设线路共',sum(ml),'km,其中' ");
        sql.append(",group_concat(dis)) ml from ( ");
        sql.append("select a.stage_id,c.`name` stage_name, ");
        sql.append("ROUND(sum(a.distance)/1000,2) ml,CONCAT(b.typename,ROUND(sum(a.distance)/1000,2),'km') dis ");
        sql.append("from p_exploration a ");
        sql.append("LEFT JOIN t_s_type b ");
        sql.append("on a.voltage_grade = b.typecode ");
        sql.append("left join p_stage c ");
        sql.append(" on a.stage_id = c.stage_id ");
        sql.append("where a.deleted =0 and b.typegroupid = 'sds807081511112c501521x1ebc24j02' ");
        sql.append("and a.exploration_person = ? ");
        sql.append("and DATE_FORMAT(NOW(),'%Y-%m-%d') = DATE_FORMAT(a.create_time,'%Y-%m-%d') ");
        sql.append("GROUP BY a.stage_id,a.voltage_grade) t ");
        sql.append("GROUP BY t.stage_id");
        return getJdbcTemplate().queryForList(sql.toString(),id);
    }

    /**
     * 获取当日踏勘点位数量
     *
     * @param id
     * @return
     */
    @Override
    public List<Map<String, Object>> getExpNumByToday(String id) {
        StringBuffer sql = new StringBuffer();
        sql.append("select t.stage_id,t.stage_name,CONCAT('采集点位共',sum(num),'个，其中', ");
        sql.append("group_concat(dis)) point_num from ( ");
        sql.append("select a.stage_id,c.`name` stage_name,CONCAT(b.`name`,count(1) ");
        sql.append(",b.unit) dis,count(1) num from p_exploration a ");
        sql.append("LEFT JOIN p_pt b ");
        sql.append("on a.pt_id = b.pt_id ");
        sql.append("left join p_stage c ");
        sql.append("on a.stage_id = c.stage_id ");
        sql.append("where a.deleted =0 and a.exploration_person = ? ");
        sql.append("and DATE_FORMAT(NOW(),'%Y-%m-%d') = DATE_FORMAT(a.create_time,'%Y-%m-%d') ");
        sql.append("GROUP BY a.pt_id) t ");
        sql.append("GROUP BY t.stage_id");
        return getJdbcTemplate().queryForList(sql.toString(),id);
    }

    /**
     * 获取当日图片采集数量
     *
     * @param id
     * @return
     */
    @Override
    public List<Map<String, Object>> getPhotoNumByToday(String id) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT b.stage_id,c.`name` stage_name,CONCAT('影像采集',count(1),'个') photo_num ");
        sql.append("from p_exploration_photo a ");
        sql.append("LEFT JOIN p_exploration b ");
        sql.append("on a.exploration_id = b.exploration_id ");
        sql.append("left join p_stage c ");
        sql.append("on b.stage_id = c.stage_id ");
        sql.append("where DATE_FORMAT(NOW(),'%Y-%m-%d') = DATE_FORMAT(a.create_time,'%Y-%m-%d') ");
        sql.append("and b.exploration_person = ? ");
        sql.append("GROUP BY b.stage_id;");
        return getJdbcTemplate().queryForList(sql.toString(),id);
    }

    /**
     * 获取当日项目统计
     *
     * @param id
     * @return
     */
    @Override
    public List<Map<String, Object>> todayProSum(String id) {
        StringBuffer sql = new StringBuffer();
        sql.append("select tb.stage_id,tb.stage_name,GROUP_CONCAT(val) project_statis from ( ");
        sql.append("SELECT b.stage_id,c.`name` stage_name,CONCAT('影像采集',count(1),'个') val ");
        sql.append("from p_exploration_photo a ");
        sql.append("LEFT JOIN p_exploration b ");
        sql.append("on a.exploration_id = b.exploration_id ");
        sql.append("left join p_stage c ");
        sql.append("on b.stage_id = c.stage_id ");
        sql.append("where DATE_FORMAT(NOW(),'%Y-%m-%d') = DATE_FORMAT(a.create_time,'%Y-%m-%d') ");
        sql.append("and b.exploration_person = ? ");
        sql.append("GROUP BY b.stage_id");
        sql.append(" UNION ");
        sql.append("select t.stage_id,t.stage_name,CONCAT('采集点位共',sum(num),'个，其中', ");
        sql.append("group_concat(dis)) val from ( ");
        sql.append("select a.stage_id,c.`name` stage_name,CONCAT(b.`name`,count(1) ");
        sql.append(",b.unit) dis,count(1) num from p_exploration a ");
        sql.append("LEFT JOIN p_pt b ");
        sql.append("on a.pt_id = b.pt_id ");
        sql.append("left join p_stage c ");
        sql.append("on a.stage_id = c.stage_id ");
        sql.append("where a.deleted =0 and a.exploration_person = ? ");
        sql.append("and DATE_FORMAT(NOW(),'%Y-%m-%d') = DATE_FORMAT(a.create_time,'%Y-%m-%d') ");
        sql.append("GROUP BY a.pt_id) t ");
        sql.append("GROUP BY t.stage_id");
        sql.append(" UNION ");
        sql.append("select t.stage_id,t.stage_name,CONCAT('踏设线路共',sum(ml),'km,其中' ");
        sql.append(",group_concat(dis)) val from ( ");
        sql.append("select a.stage_id,c.`name` stage_name, ");
        sql.append("ROUND(sum(a.distance)/1000,2) ml,CONCAT(b.typename,ROUND(sum(a.distance)/1000,2),'km') dis ");
        sql.append("from p_exploration a ");
        sql.append("LEFT JOIN t_s_type b ");
        sql.append("on a.voltage_grade = b.typecode ");
        sql.append("left join p_stage c ");
        sql.append(" on a.stage_id = c.stage_id ");
        sql.append("where a.deleted =0 and b.typegroupid = 'sds807081511112c501521x1ebc24j02' ");
        sql.append("and a.exploration_person = ? ");
        sql.append("and DATE_FORMAT(NOW(),'%Y-%m-%d') = DATE_FORMAT(a.create_time,'%Y-%m-%d') ");
        sql.append("GROUP BY a.stage_id,a.voltage_grade) t ");
        sql.append("GROUP BY t.stage_id");
        sql.append(" ) tb GROUP BY tb.stage_id ");
        return getJdbcTemplate().queryForList(sql.toString(),id,id,id);
    }

    /**
     * 获取当日项目统计
     *
     * @param id
     * @return
     */
    @Override
    public Map<String, Object> getTodayLog(String id) {
        StringBuffer sql = new StringBuffer();
        sql.append("select log_id,user_id,voice_remark,characters_remark,UNIX_TIMESTAMP(create_time) create_time ");
        sql.append("from p_log ");
        sql.append("where user_id = ? and  DATE_FORMAT(create_time,'%Y-%m-%d')=DATE_FORMAT(NOW(),'%Y-%m-%d') ");
        List<Map<String,Object>> res = getJdbcTemplate().queryForList(sql.toString(),id);
        if(res!=null&&res.size()>0){
            return res.get(0);
        }
        return null;
    }

    /**
     * 修改日志基本信息
     *
     * @param logid
     * @param voice_remark
     * @param characters_remark
     */
    @Override
    public void upLog(Long logid, String voice_remark, String characters_remark) {
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE `p_log` SET `voice_remark`=?, `characters_remark`=? WHERE (`log_id`=?)");
        getJdbcTemplate().update(sql.toString(),voice_remark,characters_remark,logid);
    }

    /**
     * 删除日志详细
     *
     * @param logid
     */
    @Override
    public void delLogDetail(Long logid) {
        StringBuffer sql = new StringBuffer();
        sql.append("DELETE from p_log_detail where log_id =?");
        getJdbcTemplate().update(sql.toString(),logid);
    }


}
